# demo01_pymysql.py
import pymysql
# 1. 连接数据库
conn = pymysql.connect(
    host="127.0.0.1", port=3306, user="root",
    passwd="tedu.cn", # mariadb没有密码的同学这一步省略
    db="tedu1", charset="utf8"
)
# 2. 创建游标(负责数据库的增删改查等操作)
cur = conn.cursor()
# ================================================
# 3.1 创建 departments 表   (表存在则不能创建第二次)
# create_dep = """CREATE TABLE departments(
#     dep_id INT, dep_name VARCHAR(50), PRIMARY KEY(dep_id)
# )"""
# cur.execute(create_dep)  # 通过游标执行sql语句
# ================================================
# 3.2 给 departments 表插入数据  (dep_id不能重复)
# insert_dep = "INSERT INTO departments VALUES (%s, %s)"
# cur.execute(insert_dep, (1, "DEV"))
# cur.execute(insert_dep, (2, "OPS"))
# cur.executemany(insert_dep, [(3, "UI"), (4, "TEST")])
# ================================================
# 3.3 查询 departments 表中的数据
# (参照码云, 完成 3.3 操作)
# select_dep = "SELECT * FROM departments"
# cur.execute(select_dep)
# result = cur.fetchmany(2)  # 获取查询结果集的两条数据
# print(result)
# result = cur.fetchone()  # 获取下一条数据
# print(result)
# result = cur.fetchall()  # 获取剩余的所有数据
# print(result)
# ================================================
# 3.4 更新数据 将 DEV 改成 HR
# update_dep = """UPDATE departments
#                 SET dep_name=%s WHERE dep_name=%s"""
# cur.execute(update_dep, ("HR", "DEV"))
# cur.execute(update_dep, ("MARKET", "OPS"))
# ================================================
# 3.5 删除数据
delete_dep = "DELETE FROM departments WHERE dep_id = %s"
cur.execute(delete_dep, (1, ))
cur.execute(delete_dep, (2, ))
# ================================================
# 4. 提交任务并且关闭资源
conn.commit()  # 提交任务
cur.close()
conn.close()

